from __future__ import print_function

import time
import openpyxl
import pymysql
import datetime
import paramiko
# 打开数据库连接

#1. 需要创建一个test_change.xlsx文件。
#2. 更改需要分的主机号，比如host84。
#3. 如果平均值太小，自己调大平均值。
#4. 把需要分的账号写入指定文件。
#5. 把需要分的主机写入文件
host_file = 'host84'
#创建空表，并定义数据格式
def create_table():
    #host_file = 'host84'
    db = pymysql.connect(host='192.168.2.29',user='zabbix',password='zabbix',database='zabbix')
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS "+host_file)
    sql_create_table="""CREATE TABLE {}(
     `account_id` INT UNSIGNED AUTO_INCREMENT,
     `mac` VARCHAR(40),
     `vlan` VARCHAR(40) NOT NULL,
     `username` VARCHAR(40) NOT NULL,
     `password` VARCHAR(40),
     `ifname` VARCHAR(40),
     `bridge` VARCHAR(40),
     `max_tx` VARCHAR(40),
     `max_rx` VARCHAR(40),
     `enable` VARCHAR(40),
     `host` VARCHAR(40),
     `date` VARCHAR(40),
     PRIMARY KEY ( `account_id` )
     )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """.format(host_file)
    try:
        cursor.execute(sql_create_table)
        db.commit()
    except:
        db.rollback()
    # 关闭数据库连接
    db.close()

#这个程序实现了把指定表格的数据拉进数据库，
#重新把分好主机的列表写入数据库
def put_table_to_DB():
    db = pymysql.connect(host='192.168.2.29',
                         user='zabbix',
                         password='zabbix',
                         database='zabbix')
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    #host_file = 'host84'
    wb = openpyxl.load_workbook('{}.xlsx'.format(host_file))
    sheet = wb.active
    # 遍历行
    # 把表格数据写入数据库
    rowName = {'mac': "", 'vlan': "", 'username': "", 'password': "", 'ifname': "", 'bridge': "", 'max_tx': "",
               'max_rx': '', 'enable': '', 'host': "", 'date': ''}
    print('一共有{}条账号，请注意核对！！'.format(sheet.max_row))
    # print()
    for j in range(1, sheet.max_row + 1):
        i = 1
        for key in rowName.keys():
            i = i + 1
            mytemp = str(sheet.cell(column=i, row=j).value)
            # 判断是不是host，不是就分离等号
            if i < 11:
                rowName[key] = mytemp.split("=")[1]
            else:
                if i == 11:
                    rowName[key] = 'None'
                    continue
                rowName[key] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                break
            # print(str(j)+key+'---'+rowName[key])
        sql = "INSERT INTO %s( mac, vlan, username,password,ifname,bridge,max_tx,max_rx,enable,host,date) \
               VALUES('%s','%s', '%s','%s','%s','%s','%s', '%s' ,'%s','%s','%s')" % \
              (host_file, rowName['mac'], rowName['vlan'], rowName['username'], rowName['password'], rowName['ifname'], \
               rowName['bridge'], rowName['max_tx'], rowName['max_rx'], rowName['enable'], rowName['host'],
               rowName['date'])
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()
    # 关闭数据库连接
    db.close()


#把所有的账号速率平均分配到主机
def account_avg():
    # 打开数据库连接
    db = pymysql.connect(host='192.168.2.29',
                         user='zabbix',
                         password='zabbix',
                         database='zabbix')
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    #host_file = 'host84'
    #读入需要分配的主机文件，
    with open('{}_list.txt'.format(host_file)) as f:
        host = f.readlines()
    sql = "select account_id, max_tx from {}".format(host_file)
    num_list=[]
    #把所有的主机列表按照顺序压入列表
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        #print('result')
        #print(results)
        for row in results:
            account_id = row[0]
            #print(account_id)
            max_tx = row[1]
            #print(max_tx)
            num_list.append(max_tx)
            # 打印结果
            #print("account_id=%s,max_tx=%s" % (account_id, max_tx))
    except:
        print("Error: unable to fetch data")
    # 关闭数据库连接
    db.close()


    #打印所有的tx
    #print(num_list)
    count=0
    print('一共有{}台主机，请注意核对'.format(len(host)))
    for i in range(1, len(num_list)):
        count+=int(num_list[i])
    avg = count / len(host)
    avg = 1600
    #打印算出的平均值
    print('平均值算出来的是 avg = '+ str(avg))

    #2825
    #把列表的值依次弹出列表，直到列表里没有值,这里应该是0
    tag_list=[]
    #设置一个value，当这个值超过平均值时，判断
    temp=0
    i=0
    #在没有
    print('在没有排列之前是{}条账号'.format(len(num_list)))
    while 0 != len(num_list):
        mypop = num_list.pop()
        temp = temp + int(mypop)
        #超过平均值，就i-1，把弹出的值加回去，从头开始。
        if i >= len(host):
            #print('is over')
            tag_list.append(host[i-1])
            break
            #否则就
        tag_list.append(host[i])
        if temp >= avg:
            print('this num is over'+str(temp))
            temp = int(mypop)
            i+=1
            continue
        #把这个放入主机列表
    print('剩余{}条账号没有排列'.format(len(num_list)))
    print('排列之后有{}个主机 '.format(len(tag_list)))
    #把主机列表tag_list写入excel
    wb = openpyxl.load_workbook('{}.xlsx'.format(host_file))
    sheet = wb['Sheet1']
    i = 0
    for row in range(1,sheet.max_row+1):
        temp = sheet.cell(row=row, column=11).value
        #print(row)
        try:
            sheet.cell(row=row, column=11).value = tag_list[i]
        except (IndexError)as err:
            print(row)
            print('请把平均值avg调整大一点，不然不够分啊！')
        i += 1
    wb.save('test_change.xlsx')
    wb.close()


def createLocalFile():
    #读入需要分配的主机文件，
    with open('{}_list.txt'.format(host_file)) as f:
        myhost = f.readlines()
    print('------------------')
    print(myhost)
    wb = openpyxl.load_workbook('test_change.xlsx')
    sheet = wb.active
    for j in range(1, sheet.max_row):
        mac = sheet.cell(column=2, row=j).value
        vlan = sheet.cell(column=3, row=j).value
        username = sheet.cell(column=4, row=j).value
        password = sheet.cell(column=5, row=j).value
        ifname = sheet.cell(column=6, row=j).value
        bridge = sheet.cell(column=7, row=j).value
        max_tx = sheet.cell(column=8, row=j).value
        max_rx = sheet.cell(column=9, row=j).value
        enable = sheet.cell(column=10, row=j).value
        host = sheet.cell(column=11, row=j).value

        # 先清空文件内容，清空remote文件夹
        with open('remote/{}_{}.txt'.format(host_file, host.split(".")[2]), 'a', encoding="utf-8") as f:
            # temp = '\t'.join([mac,vlan,username,password,ifname,bridge,max_tx,max_rx,enable,host])
            temp = '\t'.join([mac, vlan, username, password, ifname, bridge, max_tx, max_rx, enable])
            f.writelines(temp)
            f.write('\n')
def main():
    create_table()
    put_table_to_DB()
    account_avg()
    createLocalFile()
if __name__ == '__main__':
    main()
